﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using BHXH_NT.Properties;
using System.Configuration;


namespace BHXH_NT
{
    public partial class frmCapnhatdulieu : Form
    {
        DataTable dtHuyen, dtHuyenUpdate;
        public frmCapnhatdulieu()
        {
            InitializeComponent();
        }

        private void btnSolieu_Click(object sender, EventArgs e)
        {
            
            string ngayps = dtpngay.Value.Month + "/" +  dtpngay.Value.Day + "/" +  dtpngay.Value.Year;
            if (cboHuyen.SelectedIndex == 0) { return; }
            switch (cboHuyen.SelectedIndex)
            {
                case 1:
                    GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_NHATRANG_CONNECTIONTRING,
                        ModuleBHXH.huyen.NHATRANG, ngayps);
                    GetDataFromCentral(ModuleBHXH.huyen.NHATRANG, ngayps);
                     break;
                case 2:
                   GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_CAMRANH_CONNECTIONTRING,
                        ModuleBHXH.huyen.CAMRANH, ngayps);
                   GetDataFromCentral(ModuleBHXH.huyen.CAMRANH, ngayps);
                break;
                case 3:
                    GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_CAMLAM_CONNECTIONTRING,
                        ModuleBHXH.huyen.CAMLAM, ngayps);
                    GetDataFromCentral(ModuleBHXH.huyen.CAMLAM, ngayps);
                break;
               case 4:
                GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_NINHHOA_CONNECTIONTRING,
                   ModuleBHXH.huyen.NINHHOA, ngayps);
                GetDataFromCentral(ModuleBHXH.huyen.NINHHOA, ngayps);
                break;
                case 5:
                GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_VANNINH_CONNECTIONTRING,
                ModuleBHXH.huyen.VANNINH, ngayps);
                GetDataFromCentral(ModuleBHXH.huyen.VANNINH, ngayps);
                break;
                case 6:
                GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_DIENKHANH_CONNECTIONTRING,
                ModuleBHXH.huyen.DIENKHANH, ngayps);
                GetDataFromCentral(ModuleBHXH.huyen.DIENKHANH, ngayps);
                    break;
                case 7:
                    GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_KHANHVINH_CONNECTIONTRING,
                    ModuleBHXH.huyen.KHANHVINH, ngayps);
                    GetDataFromCentral(ModuleBHXH.huyen.KHANHVINH , ngayps);
                    break;
                case 8:
                    GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_KHANHSON_CONNECTIONTRING,
                   ModuleBHXH.huyen.KHANHSON, ngayps);
                    GetDataFromCentral(ModuleBHXH.huyen.KHANHSON, ngayps);
                    break;
                case 9:
                    GetDataFromHuyen(BHXH_NT.Properties.Settings.Default.MISBHXH_TINH_CONNECTIONTRING,
                    ModuleBHXH.huyen.TINH, ngayps);
                    GetDataFromCentral(ModuleBHXH.huyen.TINH, ngayps);
                    break;
                
            }
            
            FormatGrid();
            FormatGrid1();
        }
        private void btnCapnhat_Click(object sender, EventArgs e)
        {
            
            string ngayps = dtpngay.Value.Month + "/" + dtpngay.Value.Day + "/" + dtpngay.Value.Year;
            if (cboHuyen.SelectedIndex == 0) { return; }
            switch (cboHuyen.SelectedIndex)
            {
                case 1:
                    InsertData(ModuleBHXH.huyen.NHATRANG);
                    GetDataAfterInsert(ModuleBHXH.huyen.NHATRANG, ngayps);
                    break;
                case 2:
                    InsertData(ModuleBHXH.huyen.CAMRANH);
                    GetDataAfterInsert(ModuleBHXH.huyen.CAMRANH, ngayps);
                    break;
                case 3:
                    InsertData(ModuleBHXH.huyen.CAMLAM);
                    GetDataAfterInsert(ModuleBHXH.huyen.CAMLAM, ngayps);
                    break;
                case 4:
                    InsertData(ModuleBHXH.huyen.NINHHOA);
                    GetDataAfterInsert(ModuleBHXH.huyen.NINHHOA, ngayps);
                    break;
                case 5:
                   InsertData(ModuleBHXH.huyen.VANNINH);
                   GetDataAfterInsert(ModuleBHXH.huyen.VANNINH, ngayps);
                    break;
                case 6:
                   InsertData(ModuleBHXH.huyen.DIENKHANH);
                   GetDataAfterInsert(ModuleBHXH.huyen.DIENKHANH, ngayps);
                    break;
                case 7:
                   InsertData(ModuleBHXH.huyen.KHANHVINH);
                   GetDataAfterInsert(ModuleBHXH.huyen.KHANHVINH, ngayps);
                    break;
                case 8:
                    InsertData(ModuleBHXH.huyen.KHANHSON);
                    GetDataAfterInsert(ModuleBHXH.huyen.KHANHSON, ngayps);
                    break;
                case 9:
                    InsertData(ModuleBHXH.huyen.TINH);
                    GetDataAfterInsert(ModuleBHXH.huyen.TINH, ngayps);
                    break;                                  
            }
            FormatGrid1();
        }
        private void GetDataFromHuyen(string stringconnection,string huyen,string ngayps)
        {
            dbConnection conn;
            conn = new dbConnection(stringconnection);
            SqlParameter[] sqlParameter = new SqlParameter[0];
            string sql = "SELECT *,'" + huyen + "' from hosocn where ngayps >= '" + ngayps + "' and left(sobhxh,1) <> 'Y'";
            dtHuyen = conn.executeSelectQuery(sql, sqlParameter);
            fa.DataSource = dtHuyen;
            sql = "SELECT *,'" + huyen + "' from HCHOSOCN where ngay >= '" + ngayps + "' and socmnd is not null and left(sobhxh,1) <> 'Y'";
            dtHuyenUpdate = conn.executeSelectQuery(sql, sqlParameter);

        }
        private void GetDataFromCentral(string huyen, string ngayps)
        {
            dbConnection conn;


            conn = new dbConnection(ModuleBHXH.sqlConnectionString);
            SqlParameter[] sqlParameter = new SqlParameter[0];
            string sql = "SELECT *,'" + huyen + "' from hosocn where ngayps >= '" + ngayps + "' and left(sobhxh,1) <> 'Y'"
                + " and HUYEN = '" + huyen + "'";
            fa1.DataSource = conn.executeSelectQuery(sql, sqlParameter);
        }
        private void GetDataAfterInsert(string huyen, string ngayps)
        {
            
            dbConnection conn;
            conn = new dbConnection(ModuleBHXH.sqlConnectionString);
            string sql = "select ROW_NUMBER() OVER(ORDER BY SOBHXH) AS STT,* from dbo.HOSOCN where HUYEN = '" + huyen + "' and NGAYPS >= '" + ngayps + "'";
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa1.DataSource = conn.executeSelectQuery(sql, sqlParameter);
        }
        private void InsertData(string huyen)
        {
            string sql;
           
            dbConnection conn = new dbConnection();
            SqlParameter[] sqlParameter = new SqlParameter[0];
            using (SqlBulkCopy bulkCopy =
                       new SqlBulkCopy(ModuleBHXH.sqlConnectionString))
            {
                bulkCopy.DestinationTableName = "HOSOCN_TEMP";
                bulkCopy.WriteToServer(dtHuyen);
            }
            conn = new dbConnection(ModuleBHXH.sqlConnectionString);
            sql = "insert into HOSOCN "
            + " select * from dbo.HOSOCN_TEMP where HUYEN = '" + huyen + "'"
            + " and sobhxh not in (select sobhxh from HOSOCN WHERE HUYEN ='" + huyen + "')";
            conn.executeInsertQuery(sql, sqlParameter);

            sql = "delete HOSOCN_TEMP where huyen = '" + huyen + "'";
            conn.executeUpdateQuery(sql, sqlParameter);
            // update ho so ca nhan
            using (SqlBulkCopy bulkCopy =
                       new SqlBulkCopy(ModuleBHXH.sqlConnectionString))
            {
                bulkCopy.DestinationTableName = "HCHOSOCN";
                bulkCopy.WriteToServer(dtHuyenUpdate);
            }
            sql = " update dbo.HOSOCN set SOCMND = b.socmnd "
                + " from dbo.HOSOCN a inner join HCHOSOCN b "
                + " on a.sobhxh = b.sobhxh where b.huyen = '" + huyen + "'";
            conn.executeInsertQuery(sql, sqlParameter);

            sql = "delete HCHOSOCN where huyen = '" + huyen + "'";
            conn.executeUpdateQuery(sql, sqlParameter);

        }
        private void FormatGrid()
        {
            fa.Cols[4].WidthDisplay = 100;
            fa.Cols[4].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[5].WidthDisplay = 150;
            fa.Cols[5].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols["NOIKHAI"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols["DIACHIHK"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols["DIACHI"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols["NOICAP"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols["NOICAPSO"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols["MACV"].StyleDisplay.Font = new Font(".Vntime", 10);
        }
        private void FormatGrid1()
        {
            fa1.Cols[4].WidthDisplay = 100;
            fa1.Cols[4].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[5].WidthDisplay = 150;
            fa1.Cols[5].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols["NOIKHAI"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols["DIACHIHK"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols["DIACHI"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols["NOICAP"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols["NOICAPSO"].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols["MACV"].StyleDisplay.Font = new Font(".Vntime", 10);
        }

        private void btnChapnhan_Click(object sender, EventArgs e)
        {
            global::BHXH_NT.Properties.Settings.Default.Timer = dtpThoigian.Value;
            lblThongbao.Text = "Thời gian cập nhật " + dtpThoigian.Value.Hour.ToString() + ":" + dtpThoigian.Value.Minute.ToString();
            global::BHXH_NT.Properties.Settings.Default.Save();     
        }

        private void frmCapnhatdulieu_Load(object sender, EventArgs e)
        {
            lblThongbao.Text = "Thời gian cập nhật " + global::BHXH_NT.Properties.Settings.Default.Timer.Hour.ToString() + ":"
                + global::BHXH_NT.Properties.Settings.Default.Timer.Minute.ToString();
        }
        
    }
}
